package com.matt.foundation.utils;

import com.matt.domain.cookbook.Recipe;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.assertj.core.util.Arrays;
import org.joda.time.DateTime;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

public class ExcelOperateUtil<T> {

    public void writeExcel(OutputStream out,String[] names,List<T> list,String excelName) throws Exception {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(excelName);
        sheet.setDefaultColumnWidth(15);
        // sheet 对应一个工作页
        HSSFRow row = sheet.createRow(0); // 下标为0的行开始
        for (int j = 0; j < names.length; j++) {
            HSSFCell cell = row.createCell(j);
            cell.setCellValue(names[j]);
        }

        Iterator<T> it = list.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            T t = (T) it.next();
            //利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
            Field[] fields = t.getClass().getDeclaredFields();
            for (int i = 0; i < fields.length; i++) {
                HSSFCell cell = row.createCell(i);
                Field field = fields[i];
                String fieldName = field.getName();
                String getMethodName = "get"
                        + fieldName.substring(0, 1).toUpperCase()
                        + fieldName.substring(1);
                try {
                    Class tCls = t.getClass();
                    Method getMethod = tCls.getMethod(getMethodName, new Class[]{});
                    Object value = getMethod.invoke(t, new Object[]{});
                    //判断值的类型后进行强制类型转换
                    String textValue = value.toString();
                    cell.setCellValue(textValue);
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
        workbook.write(out);
    }

    public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception{
        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
        if("xls".equals(fileType)){
            wb = new HSSFWorkbook(inStr);  //2003-
        }else if("xlsx".equals(fileType)){
            wb = new XSSFWorkbook(inStr);  //2007+
        }else{
            throw new Exception("解析的文件格式有误！");
        }
        return wb;
    }



    public static void main(String[] args) throws Exception {

        File file = new File("C:\\Users\\starinside\\Desktop\\食谱导入模板.xls");
        FileInputStream inputStream = new FileInputStream(file);

        Workbook book = getWorkbook(inputStream,file.getName());
        Sheet sheet = book.getSheetAt(0);

        List<Recipe> recipeList = new ArrayList<>();

        Row row1 = sheet.getRow(1);
        for (int i = row1.getFirstCellNum() + 1; i < row1.getLastCellNum(); i++) {
            Cell cell = row1.getCell(i);
            String cellValue = cell.getStringCellValue();
            String dateString = cellValue.split("/")[1];
            int year = DateTime.now().getYear();
            Date recipeDate = DateTime.parse(year + "-" + dateString).toDate();


            //日期行下所有行
            row : for (int j = row1.getRowNum() + 1; j < sheet.getLastRowNum(); j++) {
                Row rowJ = sheet.getRow(j);
                for (int k = i; k <= i ; k++) {
                    Cell rowJCellK = rowJ.getCell(k);
                    String rowJCellKValue = rowJCellK.getStringCellValue();
                    String[] foods = rowJCellKValue.split(",");
                    if (Arrays.isNullOrEmpty(foods)) continue;

                    for (int m = 0; m < foods.length; m++) {
                        String foodName = foods[m]; //TODO: find foodId by foodName
                        if (StringUtils.isBlank(foodName)) continue;

                        Recipe recipe = new Recipe();
                        recipe.setAreaId("1");
                        recipe.setRecipeDate(recipeDate);
                        recipe.setRecipeTypeName(rowJ.getCell(rowJ.getFirstCellNum()).getStringCellValue()); //TODO: find recipeTypeId by recipeTypeName
                        recipeList.add(recipe);
                    }
                }
            }
        }
        System.out.println(recipeList);

        /*for (int i = firstRowNum; i < lastRowNum; i++){
            boolean isFirstRow = i == 0;
            if(isFirstRow) continue;

            Row row = sheet.getRow(i);
            int firstCellNum = row.getFirstCellNum();
            int lastCellNum = row.getLastCellNum();
            for (int j = firstCellNum; j < lastCellNum; j++){

                if(i == 1 && j == 0) continue;
                Cell cell = row.getCell(j);
                String cellValue =  cell.getStringCellValue();
                if (i == 1){
                    String dateString = cellValue.split("/")[1];
                    int year = DateTime.now().getYear();
                    Date recipeDate = DateTime.parse(year + "-" + dateString).toDate();
                    Recipe recipe = new Recipe();
                    recipe.setAreaId("1");
                    recipe.setRecipeDate(recipeDate);
                    recipeList.add(recipe);
                }else{
                    if(j == 0){
                        recipeList.get(j).setRecipeTypeName(cellValue);
                    }else{
                        String[] foods = cellValue.split(",");
                        for (int k = 0; k < foods.length; k++){
                            String foodName = foods[k];
                            recipeList.get(j).setFoodId((long)k);
                        }
                    }

                }

            }
        }*/



    }



    /*public static HSSFCellStyle createCellStyle(HSSFWorkbook workbook){
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);

        return style;
    }

    public static HSSFCellStyle createCellStyle1(HSSFWorkbook workbook, short index){
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);

        style.setFillForegroundColor(index);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFillBackgroundColor(index);

        return style;
    }

    public static HSSFCellStyle createCellStyle2(HSSFWorkbook workbook){
        HSSFCellStyle style = workbook.createCellStyle();

        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setWrapText(true);

        return style;
    }*/



}
